import facade
from xjlibrary.our_file_dir import BaseDir

curPath = BaseDir.get_file_dir_absolute(__file__)


class Clear(object):
    def __init__(self):
        self.configfile = BaseDir.get_new_path(curPath, "db.ini")
        self.logger = facade.get_streamlogger()
        self.mysqlutils = facade.MysqlUtiles(self.configfile, "db", logger=self.logger)
        self.setsaccnum = set()
        self.setsac = set()

    def select(self):
        sql = "select AccessionNumber from articlenew"
        rows = self.mysqlutils.SelectFromDB(sql)
        with open("./new.txt", mode="w", encoding="utf-8") as f:
            for row in rows:
                f.write(row[0] + "\n")

    def readfile(self):
        with open(r"C:\Users\xuzhu\Desktop\EI2.txt", mode="r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                self.setsaccnum.add(fLine)

    def readfile2(self):
        with open(r"C:\Users\xuzhu\Desktop\EIMysql.txt", mode="r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                self.setsac.add(fLine)

    def selectac(self):
        sql = "select AN,allnum from accnum where stat !=2 "
        rows = self.mysqlutils.SelectFromDB(sql)
        for row in rows:
            AN, allnum = row
            self.setsac.add((AN, allnum))

    def deal(self):
        for AN, allnum in self.setsac:
            sql = "SELECT count(*) FROM `ei`.`articlenew` WHERE `AccessionNumber` LIKE '{}%'".format(AN)
            rows = self.mysqlutils.SelectFromDB(sql)
            selectall = rows[0][0]
            print(selectall, allnum)
            if selectall:
                if int(selectall) < int(allnum):
                    sql = "update accnum set stat=0 where AN='{}'".format(AN)
                    self.mysqlutils.ExeSqlToDB(sql)

    def set_stat_5(self):
        """
        通过hadoop导下来确认数据是否已上
        :return:
        """
        num = 0
        with open(r"C:\Users\xuzhu\Desktop\EI.txt", mode="r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                self.setsac.add(fLine)
                num = num + 1
                if num > 10000:
                    sql = "update articlenew set stat=5 where AccessionNumber in {}".format(tuple(self.setsac))
                    self.mysqlutils.ExeSqlToDB(sql)
                    num = 0
                    self.setsac.clear()
        sql = "update articlenew set stat=5 where AccessionNumber in {}".format(tuple(self.setsac))
        self.mysqlutils.ExeSqlToDB(sql)


if __name__ == "__main__":
    clear = Clear()
    # clear.readfile()
    # clear.selectac()
    # clear.readfile2()
    # clear.deal()
    clear.set_stat_5()
    # with open(r"C:\Users\xuzhu\Desktop\EI.txt", mode="w", encoding="utf-8") as f:
    #     print(len(clear.setsac))
    #     print(len(clear.setsaccnum))
    #     setsei = clear.setsac - clear.setsaccnum
    #     for value in setsei:
    #         f.write(value+"\n")